'use strict';

const authConst = require('../constant/auth-const');
const dataConst = require('../constant/data-const');
const Service = require('egg').Service;

class MemberService extends Service {
    static genMemberCode(number, mark) {
        let now = new Date();
        let year = now.getFullYear();
        let month = now.getMonth() + 1;
        let day = now.getDate();
        let clock = year;
        if (month < 10) {
            clock += '0';
        }
        clock += month;
        if (day < 10) {
            clock += '0';
        }
        clock += day;

        let chars = ['0','1','2','3','4','5','6','7','8','9'];
        let res = '';
        for (let i = 0; i < number; i++) {
            let id = Math.ceil(Math.random() * 9);
            res += chars[id];
        }

        return mark + clock + res;
    }

    async initUserBaseSrv(userId) {
        const { app } = this;

        const memberCode = MemberService.genMemberCode(4, 'P');
        return await app.model.UserBase.create({
            user_id: userId,
            member_code: memberCode,
            register_state: dataConst.registerType.registering.value
        });
    }

    async initUserMateSrv(userId) {
        const { app } = this;

        return await app.model.UserMate.create({
            user_id: userId
        });
    }

    async editUserSrv(userId, userData) {
        const { app } = this;

        return await app.model.User.update(userData, {
            where: {
                id: userId
            }
        });
    }

    async editUserBaseSrv(userId, userData) {
        const { app } = this;

        await app.model.UserBase.update(userData, {
            where: {
                user_id: userId
            }
        });
    }

    async updateUserBonusPointsSrv(userId) {
        const { app } = this;

        const result = await app.model.UserBase.findOne({
            where: {
                user_id: userId
            }
        });

        if (result) {
            result.bonus_points += 5;
            await result.save();
            return result.bonus_points;
        }
    }

    async editUserMateSrv(userId, mateData) {
        const { app } = this;

        await app.model.UserMate.update(mateData, {
            where: {
                user_id: userId
            }
        });
    }

    async getUserSimpleSrv(userId) {
        const { ctx } = this;

        const queryString =
            `select
                usr.id, usr.nickname, usr.gender
                , concat(:serverUrl, :avatarPath, id, '/', avatar) as avatar_url
                , date(usr.birthday) as birthday
                , timestampdiff(year, usr.birthday, curdate()) as age
                from tbl_user usr
                where true
                and usr.id = :userId`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            userId
        };
        const [result] = await ctx.helper.sqlQuery(queryString, paramsData);
        return result;
    }

    async getUserDetailSrv({username, password, userId, thirdId}) {
        const { ctx } = this;

        if (password) {
            password = ctx.helper.encrypt(password);
        }

        let queryString =
            `select
                usr.id, usr.username, usr.type, usr.role_id, usr.gender, usr.nickname, usr.avatar
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , date(usr.birthday) as birthday
                , timestampdiff(year, usr.birthday, curdate()) as age
                , ub.register_state, ub.member_code, ub.body_height, ub.body_weight
                , ub.stature_id, ub.live_region, ub.birth_region, ub.marriage_state
                , ub.child_state, ub.nationality_id, ub.month_pay, ub.organization_id
                , ub.vocation_id, ub.education_id, ub.university, ub.major
                , ub.house_state, ub.loan_state, ub.vehicle_state, ub.smoke_state
                , ub.drink_state, ub.marriage_plan, ub.child_plan, ub.introduction
                , ub.avatar_verify, ub.identity_verify, ub.education_verify
                , ub.single_state, ub.complaint_state, ub.bonus_points
                from tbl_user usr
                left join tbl_user_base ub
                on usr.id = ub.user_id
                where true
                and usr.type = :type
                and usr.state = :state`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            type: dataConst.stateType.active,
            state: authConst.userType.user.value
        };

        if (userId) {
            queryString += ` and usr.id = :userId`;
            paramsData.userId = userId;
        }
        if (thirdId) {
            queryString += ` and usr.third_id = :thirdId`;
            paramsData.thirdId = thirdId;
        }
        if (username) {
            queryString += ` and usr.username = :username`;
            paramsData.username = username;
        }
        if (password) {
            queryString += ` and usr.password = :password`;
            paramsData.password = password;
        }

        const [result] = await ctx.helper.sqlQuery(queryString, paramsData);
        return result;
    }

    async getUserMateSrv(userId) {
        const { app } = this;

        return await app.model.UserMate.findOne({
            where: {
                user_id: userId
            }
        });
    }

    async getUserListSrv({queryData, limit, offset, orderName, orderType = 'asc'}) {
        const { ctx } = this;
        const {
            gender, username, nickname, phone, monthPay, educationId, registerState, memberCode,
            minAge, maxAge, minHeight, maxHeight, minWeight, maxWeight, liveRegion, birthRegion,
            marriageState, childState, houseState, loanState, vehicleState, smokeState, drinkState,
            statureId, nationalityId, avatarVerify, identityVerify, educationVerify, singleState, complainState,
            online
        } = queryData;

        let queryString =
            `select
                lrg.latest_time, usr.id, usr.username, usr.gender, usr.nickname
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , date(usr.birthday) as birthday
                , timestampdiff(year, usr.birthday, curdate()) as age
                , ub.register_state, ub.member_code, ub.body_height
                , ub.live_region, ub.month_pay, ub.education_id, ub.marriage_state
                , ub.avatar_verify, ub.identity_verify, ub.education_verify, ub.online
                from (
                select
                lr.user_id, max(updated_at) as latest_time
                from tbl_login_record lr
                group by user_id) lrg
                left join tbl_user usr
                on lrg.user_id = usr.id
                left join tbl_user_base ub
                on usr.id = ub.user_id
                where true
                and usr.type = :type
                and usr.state = :state
                and ub.single_state = :singleState`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            type: authConst.userType.user.value,
            state: dataConst.stateType.active,
            singleState: dataConst.singleType.single.value
        };

        if (username) {
            queryString += ` and usr.username = :username`;
            paramsData.username = `%${username}%`;
        }

        if (nickname) {
            queryString += ` and usr.nickname = :nickname`;
            paramsData.nickname = `%${nickname}%`;
        }

        if (phone) {
            queryString += ` and usr.phone = :phone`;
            paramsData.phone = phone;
        }

        if (gender) {
            queryString += ` and usr.gender = :gender`;
            paramsData.gender = gender;
        }

        if (monthPay) {
            queryString += ` and ub.month_pay >= :monthPay`;
            paramsData.monthPay = monthPay;
        }

        if (educationId) {
            queryString += ` and ub.education_id >= :educationId`;
            paramsData.educationId = educationId;
        }

        if (registerState) {
            queryString += ` and ub.register_state = :registerState`;
            paramsData.registerState = registerState;
        }

        if (liveRegion) {
            queryString += ` and ub.live_region like :liveRegion`;
            paramsData.liveRegion = `%${liveRegion}%`;
        }

        if (birthRegion) {
            queryString += ` and ub.birth_region like :birthRegion`;
            paramsData.birthRegion = `%${birthRegion}%`;
        }

        if (memberCode) {
            queryString += ` and ub.member_code = :memberCode`;
            paramsData.memberCode = memberCode;
        }

        if (minAge) {
            queryString += ` and timestampdiff(year, usr.birthday, curdate()) >= :minAge`;
            paramsData.minAge = minAge;
        }

        if (maxAge) {
            queryString += ` and timestampdiff(year, usr.birthday, curdate()) <= :maxAge`;
            paramsData.maxAge = maxAge;
        }

        if (minHeight) {
            queryString += ` and ub.body_height >= :minHeight`;
            paramsData.minHeight = minHeight;
        }

        if (maxHeight) {
            queryString += ` and ub.body_height <= :maxHeight`;
            paramsData.maxHeight = maxHeight;
        }

        if (minWeight) {
            queryString += ` and ub.body_weight >= :minWeight`;
            paramsData.minWeight = minWeight;
        }

        if (maxWeight) {
            queryString += ` and ub.body_weight <= :maxWeight`;
            paramsData.maxWeight = maxWeight;
        }

        if (statureId) {
            queryString += ` and ub.stature_id = :statureId`;
            paramsData.statureId = statureId;
        }

        if (nationalityId) {
            queryString += ` and ub.nationality_id = :nationalityId`;
            paramsData.nationalityId = nationalityId;
        }

        if (marriageState) {
            queryString += ` and ub.marriage_state = :marriageState`;
            paramsData.marriageState = marriageState;
        }

        if (childState) {
            queryString += ` and ub.child_state = :childState`;
            paramsData.childState = childState;
        }

        if (houseState) {
            if (parseInt(houseState) > 1) {
                queryString += ` and ub.house_state >= :houseState`;
            } else {
                queryString += ` and ub.house_state = :houseState`;
            }
            paramsData.houseState = houseState;
        }

        if (loanState) {
            queryString += ` and ub.loan_state = :loanState`;
            paramsData.loanState = loanState;
        }

        if (vehicleState) {
            queryString += ` and ub.vehicle_state = :vehicleState`;
            paramsData.vehicleState = vehicleState;
        }

        if (smokeState) {
            queryString += ` and ub.smoke_state = :smokeState`;
            paramsData.smokeState = smokeState;
        }

        if (drinkState) {
            queryString += ` and ub.drink_state = :drinkState`;
            paramsData.drinkState = drinkState;
        }

        if (avatarVerify) {
            queryString += ` and ub.avatar_verify = :avatarVerify`;
            paramsData.avatarVerify = avatarVerify;
        }

        if (identityVerify) {
            queryString += ` and ub.identity_verify = :identityVerify`;
            paramsData.identityVerify = identityVerify;
        }

        if (educationVerify) {
            queryString += ` and ub.education_verify = :educationVerify`;
            paramsData.educationVerify = educationVerify;
        }

        if (singleState) {
            queryString += ` and ub.single_state = :singleState`;
            paramsData.singleState = singleState;
        }

        if (complainState) {
            queryString += ` and ub.complaint_state = :complainState`;
            paramsData.complainState = complainState;
        }

        if (online) {
            queryString += ` and ub.online = :online`;
            paramsData.online = online;
        }

        if (orderName) {
            queryString += ` order by ${orderName} ${orderType}`;
        }

        return await ctx.helper.sqlQueryCount(queryString, paramsData, limit, offset);
    }

    async getUserAllInfoListSrv({queryData, limit, offset, orderName, orderType = 'asc'}) {
        const {ctx} = this;
        const {
            userState, username, gender, nickname, phone, registerState, memberCode, monthPay, educationId,
            age, loginAmount, latestLogin, minAge, maxAge, minHeight, maxHeight, minWeight, maxWeight,
            statureId, nationalityId, liveRegion, birthRegion, marriageState, childState, houseState, loanState,
            vehicleState, smokeState, drinkState, avatarVerify, identityVerify, educationVerify, singleState, complainState,
            online
        } = queryData;

        let queryString =
            `select
                usr.id, usr.username, usr.gender, usr.nickname, usr.phone, usr.avatar, usr.state, usr.created_at
                , ub.register_state, ub.member_code, ub.body_height, ub.live_region , ub.month_pay, ub.education_id
                , ub.body_weight, ub.stature_id, ub.birth_region, ub.nationality_id, ub.marriage_state, ub.child_state
                , ub.house_state, ub.loan_state, ub.vehicle_state, ub.smoke_state, ub.drink_state
                , ub.avatar_verify, ub.identity_verify, ub.education_verify, ub.single_state, ub.complaint_state
                , ub.bonus_points, ub.online
                , if(isnull(usr.avatar) || length(trim(usr.avatar)) < 1, null, concat(:serverUrl, :avatarPath, usr.id, '/', usr.avatar)) as avatar_url
                , date(usr.birthday) as birthday
                , timestampdiff(year, usr.birthday, curdate()) as age
                , login_amount, latest_login
                from tbl_user usr
                left join tbl_user_base ub
                on usr.id = ub.user_id
                left join (
                select
                user_id, sum(amount) as login_amount, max(updated_at) latest_login
                from tbl_login_record
                where true
                group by user_id) uh
                on usr.id = uh.user_id
                where true
                and usr.type = :type`;

        const paramsData = {
            serverUrl: dataConst.serverUrl,
            avatarPath: dataConst.avatarPath,
            type: authConst.userType.user.value
        };

        if (userState) {
            queryString += ` and usr.state = :userState`;
            paramsData.userState = userState;
        }

        if (username) {
            queryString += ` and usr.username = :username`;
            paramsData.username = `%${username}%`;
        }

        if (nickname) {
            queryString += ` and usr.nickname = :nickname`;
            paramsData.nickname = `%${nickname}%`;
        }

        if (phone) {
            queryString += ` and usr.phone = :phone`;
            paramsData.phone = phone;
        }

        if (gender) {
            queryString += ` and usr.gender = :gender`;
            paramsData.gender = gender;
        }

        if (age) {
            queryString += ` and age = :age`;
            paramsData.age = age;
        }

        if (loginAmount) {
            queryString += ` and age = :loginAmount`;
            paramsData.loginAmount = loginAmount;
        }

        if (latestLogin) {
            queryString += ` and age = :latestLogin`;
            paramsData.latestLogin = latestLogin;
        }

        if (monthPay) {
            queryString += ` and ub.month_pay >= :monthPay`;
            paramsData.monthPay = monthPay;
        }

        if (educationId) {
            queryString += ` and ub.education_id >= :educationId`;
            paramsData.educationId = educationId;
        }

        if (registerState) {
            queryString += ` and ub.register_state = :registerState`;
            paramsData.registerState = registerState;
        }

        if (memberCode) {
            queryString += ` and ub.member_code = :memberCode`;
            paramsData.memberCode = memberCode;
        }

        if (minAge) {
            queryString += ` and timestampdiff(year, usr.birthday, curdate()) >= :minAge`;
            paramsData.minAge = minAge;
        }

        if (maxAge) {
            queryString += ` and timestampdiff(year, usr.birthday, curdate()) <= :maxAge`;
            paramsData.maxAge = maxAge;
        }

        if (minHeight) {
            queryString += ` and ub.body_height >= :minHeight`;
            paramsData.minHeight = minHeight;
        }

        if (maxHeight) {
            queryString += ` and ub.body_height <= :maxHeight`;
            paramsData.maxHeight = maxHeight;
        }

        if (minWeight) {
            queryString += ` and ub.body_weight >= :minWeight`;
            paramsData.minWeight = minWeight;
        }

        if (maxWeight) {
            queryString += ` and ub.body_weight <= :maxWeight`;
            paramsData.maxWeight = maxWeight;
        }

        if (statureId) {
            queryString += ` and ub.stature_id = :statureId`;
            paramsData.statureId = statureId;
        }

        if (nationalityId) {
            queryString += ` and ub.nationality_id = :nationalityId`;
            paramsData.nationalityId = nationalityId;
        }

        if (marriageState) {
            queryString += ` and ub.marriage_state = :marriageState`;
            paramsData.marriageState = marriageState;
        }

        if (childState) {
            queryString += ` and ub.child_state = :childState`;
            paramsData.childState = childState;
        }

        if (houseState) {
            queryString += ` and ub.house_state = :houseState`;
            paramsData.houseState = houseState;
        }

        if (loanState) {
            queryString += ` and ub.loan_state = :loanState`;
            paramsData.loanState = loanState;
        }

        if (vehicleState) {
            queryString += ` and ub.vehicle_state = :vehicleState`;
            paramsData.vehicleState = vehicleState;
        }

        if (smokeState) {
            queryString += ` and ub.smoke_state = :smokeState`;
            paramsData.smokeState = smokeState;
        }

        if (drinkState) {
            queryString += ` and ub.drink_state = :drinkState`;
            paramsData.drinkState = drinkState;
        }

        if (avatarVerify) {
            queryString += ` and ub.avatar_verify = :avatarVerify`;
            paramsData.avatarVerify = avatarVerify;
        }

        if (identityVerify) {
            queryString += ` and ub.identity_verify = :identityVerify`;
            paramsData.identityVerify = identityVerify;
        }

        if (educationVerify) {
            queryString += ` and ub.education_verify = :educationVerify`;
            paramsData.educationVerify = educationVerify;
        }

        if (singleState) {
            queryString += ` and ub.single_state = :singleState`;
            paramsData.singleState = singleState;
        }

        if (complainState) {
            queryString += ` and ub.complaint_state = :complainState`;
            paramsData.complainState = complainState;
        }

        if (online) {
            queryString += ` and ub.online = :online`;
            paramsData.online = online;
        }

        if (orderName) {
            queryString += ` order by ${orderName} ${orderType}`;
        }

        return await ctx.helper.sqlQueryCount(queryString, paramsData, limit, offset);
    }

    async getUserStatisticsDataSrv() {
        const { ctx } = this;

        const queryString =
            `select
                sum((if(usr.type = :userType, 1, 0))) as member_count
                , sum(if(ub.register_state = :registerState, 1, 0)) as registered_count
                , sum(if(ub.single_state = :singleState, 1, 0)) as single_count
                , sum(if(ub.online = :online, 1, 0)) as online_count
                from tbl_user usr
                left join tbl_user_base ub
                on usr.id = ub.user_id
                where true`;

        const paramsData = {
            userType: authConst.userType.user.value,
            registerState: dataConst.registerType.registered.value,
            singleState: dataConst.singleType.single.value,
            online: dataConst.stateType.active
        };

        const [result] = await ctx.helper.sqlQuery(queryString, paramsData);
        return result;
    }
}

module.exports = MemberService;
